1. Technical Field
Present invention embodiments relate to On-Line Analytical Processing (OLAP), and more specifically, to removing non-existent tuples from cross-joins of plural hierarchies of a dimension.
2. Discussion of Related Art
OLAP systems allow users to interactively analyse and aggregate multidimensional data. An OLAP database can be conceptualized as a multidimensional array called a “cube.” The elements of the cube are “cells” containing a set of numerical “measures.” A cell is identified by a tuple of “members” or “data attributes” designating regions of each dimension. For example, in the business domain, sales data may have a time dimension and a geographic area dimension. The time dimension may have a member for each month and the geographic area dimension may have a member for each city. The tuple, (August of 2010, Chicago), identifies a cell of the cube that contains the corresponding set of measures (e.g., unit sales, gross revenue, etc.).
The members of each dimension may be arranged in one or more hierarchies. For example, a geographic area dimension may have a hierarchy that includes levels of Country, State, and City. A time dimension may have levels that include Year, Month, and Day. Hierarchies allow a user to easily request aggregate data at various levels of granularity. By way of example, a user may request data aggregated over a given year and a given state by specifying a tuple, such as (2011, Illinois).
A dimension may have plural hierarchies that share levels. For example, a time dimension may have a Fiscal Calendar hierarchy with levels of Year, Quarter, Month, and Day, while a Regular Calendar hierarchy may include levels of Year, Month, and Day.
A cross-join request may include plural hierarchies of the same dimension that share levels, but not common data, thereby resulting in a request for data that cannot possibly exist. For example, a user may request a cross-join of the members [Fiscal Calendar].[2011].[Q1] and [Regular Calendar].[2011].[January]. These members do not share a common month if the fiscal calendar year begins in July. Thus, no data can satisfy this request. Displaying cells for which no data can possibly exist can impact end-user productivity because users have to disregard those cells.
OLAP reporting tools can apply “null suppression” to remove cells having no data from a visual display. However, null suppression is performed after the query has been processed, and operates on the entire cross-join set of tuples specified by a crosstab report. In another approach, a system may filter a dimension for members where all member attribute values equal those in at least one of the tuples and for which there exists data in the fact table. However, this involves additional SQL queries against the underlying database to check for data existence.